﻿using DBToExcel.Model;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBToExcel.Code
{
    public class BulidExcelUtils
    {
        MTempAllInfo _TempInfo;
        Dictionary<string, DataColumn> _Cols;
        IWorkbook _Workbook;
        ISheet _Sheet;
        DataTable _DataTable;
        int _StartRowIndex;
        Dictionary<string, ICellStyle> _CellStyles;
        const string _jstemp = "function getcellvalue(){{{0}}} getcellvalue()";
        public event Action<string> ProcessingEvent;
        public BulidExcelUtils(MTempAllInfo mTempAllInfo)
        {
            _TempInfo = mTempAllInfo;
        }


        /// <summary>
        /// 获取模板Excel
        /// </summary>
        /// <returns></returns>
        private IWorkbook GetWorkBook()
        {
            IWorkbook workbook = null;
            if (_TempInfo.Template.IsTempLate == 1)
            {
                Stream stream = File.Open(_TempInfo.Template.TempPlateExcelFile, FileMode.Open);
                if (_TempInfo.Template.OrgFileName.Contains(".xlsx"))
                {
                    workbook = new XSSFWorkbook(stream);
                }
                else
                {
                    workbook = new HSSFWorkbook(stream);
                }
            }
            else
            {
                workbook = new XSSFWorkbook();
            }

            return workbook;
        }

        /// <summary>
        /// 构建
        /// </summary>
        /// <returns></returns>
        public MemoryStream Bulid()
        {
            ShowProcessing("开始进行构建Excel......");

            ShowProcessing("开始加载相关环境数据......");
            LoadData();
            ShowProcessing("相关环境数据加载完成......");

            ShowProcessing("开始构建Excel......");
            MemoryStream ms = BulidExcel();
            return ms;
        }

        /// <summary>
        /// 加载数据
        /// </summary>
        private void LoadData()
        {
            _Workbook = GetWorkBook();
            ShowProcessing("初始化工作簿成功");
            if (_TempInfo.Template.IsTempLate == 1)
            {
                _Sheet = _Workbook.GetSheetAt(0);
            }
            else
            {
                _Sheet = _Workbook.CreateSheet();
            }
            ShowProcessing("开始从数据库加载数据");
            _DataTable = GetData();
            _Cols = new Dictionary<string, DataColumn>();
            foreach (DataColumn item in _DataTable.Columns)
            {
                _Cols.Add(item.Caption, item);
            }
            _CellStyles = new Dictionary<string, ICellStyle>();
            ShowProcessing("获取数据成功，总计：" + _DataTable.Rows.Count + "条");
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        private DataTable GetData()
        {
            MDBConn conn = ExcelBuilderService.QueryAllMDBConns().FirstOrDefault(x => x.Id == _TempInfo.Template.ConnId);
            if (conn == null)
            {
                throw new Exception("不存在此连接信息");
            }

            IDbConnection connection = DBConnUtils.GetConn(conn.FileName, conn.ClassName, conn.ConnStr);
            DataTable dataTable = ExcelBuilderService.QueryDataTable(connection, _TempInfo.Template.DataSql);
            return dataTable;
        }

        /// <summary>
        /// 构建Excel
        /// </summary>
        private MemoryStream BulidExcel()
        {
            if (_TempInfo.Template.IsTempLate == 0)
            {
                AddHead();
                _StartRowIndex = 1;
                ShowProcessing("此次生成非基于模板创建");
            }
            else
            {
                _StartRowIndex = _TempInfo.Template.StartRowIndex;
                ShowProcessing("此次生成基于模板创建");
            }

            try
            {
                ShowProcessing("开始填充数据......");
                SetExcelData();
                ShowProcessing("填充数据完成......");
            }
            catch (Exception ex)
            {

                ShowProcessing(string.Format("填充数据异常，异常信息：{0},堆栈信息：{1}", ex.Message, ex.StackTrace));

                return null;
            }



            using (MemoryStream ms = new MemoryStream())
            {
                _Workbook.Write(ms);
                ms.Flush();
                return ms;
            }
        }


        private void SetExcelData()
        {
            MRowInfo rowInfo = new MRowInfo();
            rowInfo.NowRowData = new Dictionary<string, object>();
            rowInfo.PreRowData = new Dictionary<string, object>();
            rowInfo.ExcelData = new List<Dictionary<string, object>>();
            rowInfo.PrintLogEvent += ShowProcessing;
            List<string> colList = _Cols.Keys.ToList();
            rowInfo.Cells = colList;
            bool addRowIndex;
            for (int i = 0; i < _DataTable.Rows.Count; i++)
            {
                DataRow row = _DataTable.Rows[i];
                addRowIndex = true;
                IRow dataRow = _Sheet.CreateRow(_StartRowIndex);
                rowInfo.NowRowIndex = _StartRowIndex;
                rowInfo.NowRowData.Clear();
                rowInfo.PreRowData.Clear();

                //当前行数据
                foreach (string col in colList)
                {
                    rowInfo.NowRowData.Add(col, row[col]);
                }

                //上一行数据
                if (i > 0)
                {
                    foreach (string col in colList)
                    {
                        rowInfo.PreRowData.Add(col, _DataTable.Rows[i - 1][col]);
                    }
                }

                Dictionary<string, object> data = new Dictionary<string, object>();
                foreach (var map in _TempInfo.Map)
                {
                    if (string.IsNullOrEmpty(map.ExcelCellIndex))
                    {
                        continue;
                    }

                    int nowCellIndex = Convert.ToInt32(map.ExcelCellIndex);
                    rowInfo.NowFieldName = map.FieldName;
                    rowInfo.NowCellValue = row[map.FieldName];
                    rowInfo.NowCellIndex = nowCellIndex;
                    object value = null;
                    if (string.IsNullOrEmpty(map.Script))
                    {
                        value = row[map.FieldName];
                    }
                    else
                    {
                        dynamic returnResult = ScriptUtil.Execute<MReturnResult>(string.Format(_jstemp, map.Script), new Dictionary<string, object>() { { "RowInfo", rowInfo }, { "Tools", new Tools() } }) as dynamic;
                        if (returnResult.CancelNowRow)
                        {
                            _Sheet.RemoveRow(dataRow);
                            addRowIndex = false;
                            break;
                        }

                        value = returnResult.Value;
                    }

                    SetCellValue(dataRow.CreateCell(nowCellIndex), value, map.FieldName);

                    data.Add(map.FieldName, value);
                }

                rowInfo.ExcelData.Add(data);
                if (addRowIndex)
                {
                    _StartRowIndex++;
                }
            }
        }

        /// <summary>
        /// 创建表头
        /// </summary>
        private void AddHead()
        {
            //填充表头
            IRow dataRow = _Sheet.CreateRow(0);
            foreach (MExcelMap column in _TempInfo.Map)
            {
                if (string.IsNullOrEmpty(column.FieldName))
                {
                    continue;
                }
                dataRow.CreateCell(Convert.ToInt32(column.ExcelCellIndex)).SetCellValue(column.FieldName);
            }
        }

        /// <summary>
        /// 处理信息
        /// </summary>
        /// <param name="log"></param>
        private void ShowProcessing(string log)
        {
            if (ProcessingEvent != null)
            {
                ProcessingEvent(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "  " + log);
            }
        }

        /// <summary>
        /// 设置值
        /// </summary>
        /// <param name="newCell"></param>
        /// <param name="value"></param>
        /// <param name="fieldName"></param>
        private void SetCellValue(ICell newCell, object value, string fieldName)
        {
            if (value == null)
            {
                newCell.SetCellValue("");
                return;
            }

            string drValue = value.ToString();
            switch (_Cols[fieldName].DataType.ToString())
            {
                case "System.Int16"://整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                case "System.Decimal"://浮点型
                case "System.Double":
                case "System.String"://字符串类型
                case "System.Boolean"://布尔型
                case "System.DBNull"://空值处理
                    newCell.SetCellValue(drValue);
                    newCell.SetCellType(CellType.String);
                    newCell.CellStyle = GetCellStyle("@");//格式化显示
                    break;
                case "System.DateTime"://日期类型
                    newCell.SetCellValue((DateTime)value);
                    newCell.CellStyle = GetCellStyle("yyyy-MM-dd HH:mm:ss");//格式化显示
                    break;
                default:
                    newCell.SetCellValue("");
                    break;
            }
        }

        private ICellStyle GetCellStyle(string part)
        {
            if (_CellStyles.ContainsKey(part))
            {
                return _CellStyles[part];
            }

            ICellStyle dateStyle = _Workbook.CreateCellStyle();
            IDataFormat format = _Workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat(part);
            _CellStyles.Add(part, dateStyle);
            return _CellStyles[part];
        }
    }
}
